package com.base.dialects;

import com.base.vo.Field;

import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author wnhuang
 * @Package com.base.dialects
 * @date 2021/7/9 10:52
 */
public class YoYSqlBuildUtil {
    private final static String GROUP_BY = "GROUP BY";

    public static String buildYoYSql(Dialects dialects, String sql, List<Field> selectList) {
        String selectSql = parseSelect(dialects, selectList);
        String whereSql = parseWhere(dialects, selectList);
        String newSql = "with subt as ( " + sql + "),subt2 as ( " + sql + " ) select " + selectSql + " " + whereSql;
        return newSql;
    }

    private static String parseSelect(Dialects dialects, List<Field> selects) {
        StringBuilder selectSql = new StringBuilder();
        List<Field> collect = selects.stream().filter(t -> t.getGroup()).collect(Collectors.toList());
        boolean flag = false;
        for (Field field1 : collect) {
            selectSql.append("a.\"").append(field1.getName()).append("\",");
            if (field1.getDataType() == 4 && "_ym".equals(field1.getDateTimeSub())) {
                flag = true;
            }
        }
        Field field = selects.stream().filter(t -> !t.getGroup()).findFirst().get();
        String fieldName = "\"" + field.getName() + "\"";
        selectSql.append("a.").append(fieldName).append(",");
        selectSql.append("case when b.").append(fieldName).append(" is null then a.").append(fieldName).append("-0 else a.").append(fieldName).append(" - b.").append(fieldName).append(" end \"YoYValue\",");
        selectSql.append("case when b.").append(fieldName).append("=0 then 0 when b.").append(fieldName).append(" is null then 100.0 else (a.").append(fieldName).append("-b.").append(fieldName).append("*1.0)/b.").append(fieldName).append("*100.0 end \"YoYRate\"");
        if (flag) {
            selectSql.append(",(a.").append(fieldName).append(" - c.").append(fieldName).append(") as QoQValue,");
            selectSql.append("case when c.").append(fieldName).append("=0 then 0 when c.").append(fieldName).append(" is null then 100.0 else (a.").append(fieldName).append("-c.").append(fieldName).append("*1.0)/c.").append(fieldName).append("*100.0 end \"QoQRate\"");
        }
        return selectSql.toString();
    }

    private static String parseWhere(Dialects dialects, List<Field> selects) {
        List<Field> fields = selects.stream().filter(t -> t.getGroup()).collect(Collectors.toList());
        String fromSql = " from subt a left join subt2 b on ";

        List<String> sql = new ArrayList<>();
        String temp = "";
        for (Field field : fields) {
            if (field.getDataType() == 4) {
                sql.add("cast(a.\"" + field.getName() + "\"as numeric) - 100 = cast(b.\"" + field.getName() + "\"as numeric)");
                if ("_ym".equals(field.getDateTimeSub())) {
                    temp += " left join (select * from subt union all select * from subt2) c on a.\"" + field.getName() + "\"=" + dialects.convertDateToString(dialects.computedDateString(dialects.convertStringToDate("c.\"" + field.getName() + "\"||'01'", "yyyyMMdd"), true, 1, "month"), "yyyyMM");
                }
            } else {
                sql.add("a.\"" + field.getName() + "\"=b.\"" + field.getName() + "\"");
            }
        }
        if (!sql.isEmpty()) {
            fromSql += String.join(" and ", sql);
            fromSql += temp;
        } else {
            fromSql += " 1=1 ";
        }
        return fromSql;
    }
}
